﻿// --------------------------------------------------------- 
//  This class is responsible for accessing the Subscriber-related data from the database
//
// --------------------------------------------------------- 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using eCitizen.DataContracts;

namespace eCitizen.DataAccess
{

    public class SubscriberInfoService : dbService
    {
        // Grab Db Connection String upon Initialize
        public SubscriberInfoService() : base() {}

        #region "Get Subscriber Information from Subscriber DB"

        public Subscriber GetSuscriberByNRIC(string subscriberNRIC)
        {
            SqlConnection dbConnection = new SqlConnection(subscriberDBConnString);
            Subscriber subscriberInfo = new Subscriber();

            // Open Db Connection
            dbConnection.Open();

            subscriberInfo = GetSubscriberbyNRIC(subscriberNRIC, dbConnection);

            // Close Db Connection
            dbConnection.Close();

            return subscriberInfo;

        }
        public List<Subscriber> GetSubscriberByNRICList(List<string> subscriberNRICList)
        {
            // ----- StopWatcher -----
            Stopwatch watch = new Stopwatch();
            watch.Start();
            // ----- StopWatcher -----

            List<Subscriber> subscriberList = new List<Subscriber>();
            SqlConnection dbConnection = new SqlConnection(subscriberDBConnString);
            Subscriber subscriberInfo = null;

            // Open Db Connection
            dbConnection.Open();

            foreach (String subscriberNRIC in subscriberNRICList)
            {
                subscriberInfo = GetSubscriberbyNRIC(subscriberNRIC, dbConnection);
                subscriberList.Add(subscriberInfo);
            }

            // Close Db Connection
            dbConnection.Close();

            // ----- StopWatcher -----
            watch.Stop();
            // ----- StopWatcher -----

            return subscriberList;

           
        }

        public List<Subscriber> GetSubscriberByProfile(SubscriberProfile subScriberCriteria)
        {
            // ----- StopWatcher -----
            Stopwatch watch = new Stopwatch();
            watch.Start();
            // ----- StopWatcher -----
            List<Subscriber> subscriberList = new List<Subscriber>();
            Subscriber subScriberInfo = null;

            SqlConnection dbConnection = new SqlConnection(subscriberDBConnString);

            SqlCommand dbCommand = new SqlCommand();
            IDataReader dbReader = null;
            SqlParameter dbParameter = null;

            dbConnection.Open();

            dbCommand.Connection = dbConnection;
            dbCommand.CommandText = "Subscriber.usp_GetSubscriberByProfile";
            dbCommand.CommandType = CommandType.StoredProcedure;

            dbParameter = new SqlParameter();
            dbParameter.ParameterName = "@Gender";
            if(!String.IsNullOrEmpty(subScriberCriteria.Gender)) dbParameter.Value = subScriberCriteria.Gender;
            dbCommand.Parameters.Add(dbParameter);

            dbParameter = new SqlParameter();
            dbParameter.ParameterName = "@AgeRangeStartDate";
            dbParameter.Value = subScriberCriteria.AgeRangeStartDate;
            dbCommand.Parameters.Add(dbParameter);

            dbParameter = new SqlParameter();
            dbParameter.ParameterName = "@AgeRangeEndDate";
            dbParameter.Value = subScriberCriteria.AgeRangeEndDate;
            dbCommand.Parameters.Add(dbParameter);

            dbParameter = new SqlParameter();
            dbParameter.ParameterName = "@MaritalStatus";
            if (!String.IsNullOrEmpty(subScriberCriteria.MaritalStatus)) dbParameter.Value = subScriberCriteria.MaritalStatus;
            dbCommand.Parameters.Add(dbParameter);

            dbParameter = new SqlParameter();
            dbParameter.ParameterName = "@ResidentialStatus";
            if (!String.IsNullOrEmpty(subScriberCriteria.ResidentialStatus)) dbParameter.Value = subScriberCriteria.ResidentialStatus;
            dbCommand.Parameters.Add(dbParameter);

            dbParameter = new SqlParameter();
            dbParameter.ParameterName = "@EmploymentStatus";
            if (!String.IsNullOrEmpty(subScriberCriteria.EmploymentStatus)) dbParameter.Value = subScriberCriteria.EmploymentStatus;
            dbCommand.Parameters.Add(dbParameter);

            dbReader = dbCommand.ExecuteReader();

            while (dbReader.Read())
            {
                subScriberInfo = new Subscriber();
                subScriberInfo.SubscriberId = dbReader.GetInt32(dbReader.GetOrdinal("UserId")).ToString();
                subScriberInfo.NRIC = dbReader.GetString(dbReader.GetOrdinal("NRIC"));
                subScriberInfo.GivenName = dbReader.GetString(dbReader.GetOrdinal("GivenName"));
                subScriberInfo.DateOfBirth = dbReader.GetDateTime(dbReader.GetOrdinal("DOB"));
                subScriberInfo.MaritalStatus = dbReader.GetInt16(dbReader.GetOrdinal("MaritalStatus"));
                subScriberInfo.ResidentialStatus = dbReader.GetInt16(dbReader.GetOrdinal("ResidentialStatus"));
                subScriberInfo.EmploymentStatus = dbReader.GetInt16(dbReader.GetOrdinal("EmploymentStatus"));
                subScriberInfo.HandPhone = dbReader.GetString(dbReader.GetOrdinal("HandPhone"));
                subScriberInfo.HomePhone = dbReader.GetString(dbReader.GetOrdinal("HomePhone"));
                subScriberInfo.OfficePhone = dbReader.GetString(dbReader.GetOrdinal("OfficePhone"));
                subScriberInfo.Email = dbReader.GetString(dbReader.GetOrdinal("Email"));

                subscriberList.Add(subScriberInfo);
            }

            dbReader.Close();
            dbConnection.Close();

            // ----- StopWatcher -----
            watch.Stop();
            // ----- StopWatcher -----
            return subscriberList;
        }
    
        private Subscriber GetSubscriberbyNRIC(string NRIC,SqlConnection dbConnection)
        {
            Subscriber subscriberInfo = new Subscriber();
            SqlCommand dbCommand = new SqlCommand();
            IDataReader dbReader = null;
            SqlParameter dbParameter = null;

            
            dbCommand.Connection = dbConnection;
            dbCommand.CommandText = "Subscriber.usp_GetSubscriberByNRIC";
            dbCommand.CommandType = CommandType.StoredProcedure;

            dbParameter = new SqlParameter();
            dbParameter.ParameterName = "@SubscriberNRIC";
            dbParameter.Value = NRIC;

            dbCommand.Parameters.Add(dbParameter);

            dbReader = dbCommand.ExecuteReader();


            while (dbReader.Read())
            {
                subscriberInfo.SubscriberId = dbReader.GetInt32(dbReader.GetOrdinal("UserId")).ToString();
                subscriberInfo.NRIC = dbReader.GetString(dbReader.GetOrdinal("NRIC"));
                subscriberInfo.GivenName = dbReader.GetString(dbReader.GetOrdinal("GivenName"));
                subscriberInfo.DateOfBirth = dbReader.GetDateTime(dbReader.GetOrdinal("DOB"));
                subscriberInfo.MaritalStatus = dbReader.GetInt16(dbReader.GetOrdinal("MaritalStatus"));
                subscriberInfo.ResidentialStatus = dbReader.GetInt16(dbReader.GetOrdinal("ResidentialStatus"));
                subscriberInfo.EmploymentStatus = dbReader.GetInt16(dbReader.GetOrdinal("EmploymentStatus"));
                subscriberInfo.HandPhone = dbReader.GetString(dbReader.GetOrdinal("HandPhone"));
                subscriberInfo.HomePhone = dbReader.GetString(dbReader.GetOrdinal("HomePhone"));
                subscriberInfo.OfficePhone = dbReader.GetString(dbReader.GetOrdinal("OfficePhone"));
                subscriberInfo.Email = dbReader.GetString(dbReader.GetOrdinal("Email"));
            }

            dbReader.Close();

            return subscriberInfo;
        }

        public List<Subscriber> GetSubscriberbySubscriptionList(List<Subscriber> subscriptionList)
        {
            List<Subscriber> subscriberList = new List<Subscriber>();

            SqlConnection dbConnection = new SqlConnection(subscriberDBConnString);
            Subscriber subscriberInfo = null;

            // Open Db Connection
            dbConnection.Open();

            foreach (Subscriber subscriber in subscriptionList)
            {
                subscriberInfo = GetSubscriberbySubscriberId(subscriber, dbConnection);
                subscriberList.Add(subscriberInfo);
            }

            // Close Db Connection
            dbConnection.Close();

            return subscriberList;

        }

        private Subscriber GetSubscriberbySubscriberId(Subscriber subscriberInfo, SqlConnection dbConnection)
        {
            SqlCommand dbCommand = new SqlCommand();
            IDataReader dbReader = null;
            SqlParameter dbParameter = null;


            dbCommand.Connection = dbConnection;
            dbCommand.CommandText = "Subscriber.usp_GetSubscriberById";
            dbCommand.CommandType = CommandType.StoredProcedure;

            dbParameter = new SqlParameter();
            dbParameter.ParameterName = "@SubscriberId";
            dbParameter.Value = subscriberInfo.SubscriberId;
            dbCommand.Parameters.Add(dbParameter);

            dbReader = dbCommand.ExecuteReader();

            while (dbReader.Read())
            {
                subscriberInfo.NRIC = dbReader.GetString(dbReader.GetOrdinal("NRIC"));
                subscriberInfo.GivenName = dbReader.GetString(dbReader.GetOrdinal("GivenName"));
                subscriberInfo.DateOfBirth = dbReader.GetDateTime(dbReader.GetOrdinal("DOB"));
                subscriberInfo.Gender = dbReader.GetInt16(dbReader.GetOrdinal("Gender"));
                subscriberInfo.MaritalStatus = dbReader.GetInt16(dbReader.GetOrdinal("MaritalStatus"));
                subscriberInfo.ResidentialStatus = dbReader.GetInt16(dbReader.GetOrdinal("ResidentialStatus"));
                subscriberInfo.EmploymentStatus = dbReader.GetInt16(dbReader.GetOrdinal("EmploymentStatus"));
                subscriberInfo.HandPhone = dbReader.GetString(dbReader.GetOrdinal("HandPhone"));
                subscriberInfo.HomePhone = dbReader.GetString(dbReader.GetOrdinal("HomePhone"));
                subscriberInfo.OfficePhone = dbReader.GetString(dbReader.GetOrdinal("OfficePhone"));
                subscriberInfo.Email = dbReader.GetString(dbReader.GetOrdinal("Email"));
            }
            dbReader.Close();
            return subscriberInfo;
        }

        #endregion

        #region "Get Subscriptions from the eCitizen DB"
        
        public List<Subscriber> GetSubscriptionbySubscriberList(List<Subscriber> subscriberInfoList)
        {
            // ----- StopWatcher -----
            Stopwatch watch = new Stopwatch();
            watch.Start();
            // ----- StopWatcher -----
            List<Subscriber> subscriptionList = new List<Subscriber>();
            
            SqlConnection dbConnection = new SqlConnection(ecitizenPersonalisedDBConnString);
            dbConnection.Open();
            foreach(Subscriber subscriberInfo in subscriberInfoList)
            {
                Subscriber subscriber = GetSubscription(subscriberInfo, dbConnection);
                subscriptionList.Add(subscriber);
            }
            dbConnection.Close();
            // ----- StopWatcher -----
            watch.Stop();
            // ----- StopWatcher -----
            return subscriptionList;

        }

        private Subscriber GetSubscription(Subscriber subscriberInfo, SqlConnection dbConnection)
        {
            SqlCommand dbCommand = new SqlCommand();
            IDataReader dbReader = null;
            SqlParameter dbParameter = null;

           
            dbCommand.Connection = dbConnection;
            dbCommand.CommandText = "eCitizen_Personalised.usp_GetSubscriptionBySubscriberId";
            dbCommand.CommandType = CommandType.StoredProcedure;

            dbParameter = new SqlParameter();
            dbParameter.ParameterName = "@SubscriberId";
            dbParameter.Value = subscriberInfo.SubscriberId;
            dbCommand.Parameters.Add(dbParameter);

            dbParameter = new SqlParameter();
            dbParameter.ParameterName = "@AgencyServiceCode";
            dbParameter.Value = subscriberInfo.SubscriptionAgencyCode;
            dbCommand.Parameters.Add(dbParameter);

            dbReader = dbCommand.ExecuteReader();


            while (dbReader.Read())
            {
                subscriberInfo.SmsSubscription = dbReader.GetBoolean(dbReader.GetOrdinal("SmsSubscription"));
                subscriberInfo.EmailSubscription = dbReader.GetBoolean(dbReader.GetOrdinal("EmailSubscription"));
            }
            dbReader.Close();
            return subscriberInfo;
        }

        public List<Subscriber> GetSubscriptionbyServiceCode(string agencyServiceCode)
        {
            List<Subscriber> subscriptionList = new List<Subscriber>();
            SqlConnection dbConnection = new SqlConnection(ecitizenPersonalisedDBConnString);
            dbConnection.Open();

            SqlCommand dbCommand = new SqlCommand();
            IDataReader dbReader = null;
            SqlParameter dbParameter = null;


            dbCommand.Connection = dbConnection;
            dbCommand.CommandText = "eCitizen_Personalised.usp_GetSubscriptionsByServiceCode";
            dbCommand.CommandType = CommandType.StoredProcedure;

            dbParameter = new SqlParameter();
            dbParameter.ParameterName = "@AgencyServiceCode";
            dbParameter.Value = agencyServiceCode;
            dbCommand.Parameters.Add(dbParameter);

            dbReader = dbCommand.ExecuteReader();


            while (dbReader.Read())
            {
                Subscriber subscriberInfo = new Subscriber();
                subscriberInfo.SubscriberId = dbReader.GetInt32(dbReader.GetOrdinal("SubscriberId")).ToString();
                subscriptionList.Add(subscriberInfo);
            }

            dbReader.Close();
            dbConnection.Close();

            return subscriptionList;
        }
        
        #endregion

    }
}